﻿using System.Data;

namespace WorkOrder.Business
{
    public class BillAvgTime
    {
        DbHelper dbhelper = new DbHelper();

        #region 开通单平均历时

        /// <summary>
        /// 工单平均历时
        /// </summary>
        /// <param name="startTimeOfCreate">派单时间起</param>
        /// <param name="endTimeOfCreate">派单时间止</param>
        /// <param name="startTimeOfFinish">完成时间起</param>
        /// <param name="endTimeOfFinish">完成时间止</param>
        /// <param name="orderType">1:开通，2:取消，3:调整</param>
        /// <param name="condition">条件，从CRM资源确认开始统计：CRM；从派单时间开始统计：CREATE</param>
        /// <returns>返回数据表不包含中文列名</returns>
        public DataTable GetAvgTime(string startTimeOfCreate, string endTimeOfCreate, string startTimeOfFinish, string endTimeOfFinish, int orderType, string condition)
        {
            string sqlstr = "";
            if (condition == "CRM")
                sqlstr += "select B.product_name,A.order_id,dbo.f_WorkDay(A.confirm_time,A.finish_time)";
            if (condition == "CREATE")
                sqlstr += "select B.product_name,A.order_id,dbo.f_WorkDay(A.task_time,A.finish_time)";
            sqlstr +=
                " as lishi,A.product_id into #T from table_work_order A ,Table_Product B ";
            if (!string.IsNullOrEmpty(startTimeOfCreate) && !string.IsNullOrEmpty(endTimeOfCreate) && !string.IsNullOrEmpty(startTimeOfFinish) && !string.IsNullOrEmpty(endTimeOfFinish))
                sqlstr += "where A.product_id=B.product_id and A.finish_time between '" + startTimeOfFinish +
                          " 0:0:0' and '" + endTimeOfFinish + " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') and A.task_time between '" + startTimeOfCreate +
                          " 0:0:0' and '" + endTimeOfCreate + " 23:59:59' ";
            else if (!string.IsNullOrEmpty(startTimeOfCreate) && !string.IsNullOrEmpty(endTimeOfCreate) && (string.IsNullOrEmpty(startTimeOfFinish) || string.IsNullOrEmpty(endTimeOfFinish)))
                sqlstr += "where A.product_id=B.product_id and A.task_time between '" + startTimeOfCreate +
                          " 0:0:0' and '" + endTimeOfCreate + " 23:59:59' ";
            else if ((string.IsNullOrEmpty(startTimeOfCreate) || string.IsNullOrEmpty(endTimeOfCreate)) && !string.IsNullOrEmpty(startTimeOfFinish) && !string.IsNullOrEmpty(endTimeOfFinish))
                sqlstr += "where A.product_id=B.product_id and A.finish_time between '" + startTimeOfFinish +
                          " 0:0:0' and '" + endTimeOfFinish + " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') ";

            sqlstr += "and A.is_back=0 and A.order_type_id=" + orderType + ";   " +
                      "select product_name,cast(avg(lishi) as decimal(10,2)) as avg_time,product_id from #T group by product_name,product_id;   " +
                      "drop table #T";
            return dbhelper.Query("", sqlstr);
        }
        #endregion


    }
}